CREATE TABLE yy_job (
  id BIGSERIAL PRIMARY KEY,
  project_code VARCHAR(64) NOT NULL,
  module_code VARCHAR(64) NOT NULL,
  module_name VARCHAR(64) NOT NULL,
  job_code VARCHAR(64) NOT NULL,
  job_name VARCHAR(64) NOT NULL,
  job_status CHAR(1) NOT NULL,
  execute_type CHAR(1) NOT NULL,
  business_no VARCHAR(64) NOT NULL,
  expected_start_time TIMESTAMP DEFAULT NULL,
  exec_time TIMESTAMP DEFAULT NULL,
  exec_count INT DEFAULT NULL,
  version INT NOT NULL DEFAULT 1,
  creator VARCHAR(64) NOT NULL,
  gmt_created TIMESTAMP NOT NULL,
  modifier VARCHAR(64) NOT NULL,
  gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  is_deleted CHAR(1) NOT NULL DEFAULT 'N'
);

COMMENT ON TABLE yy_job IS '作业表';
COMMENT ON COLUMN yy_job.id IS '主键';
COMMENT ON COLUMN yy_job.project_code IS '项目空间代码';
COMMENT ON COLUMN yy_job.module_code IS '模块代码';
COMMENT ON COLUMN yy_job.module_name IS '模块名称';
COMMENT ON COLUMN yy_job.job_code IS '任务代码';
COMMENT ON COLUMN yy_job.job_name IS '任务名称';
COMMENT ON COLUMN yy_job.job_status IS '任务状态，C创建，L锁定，F失败，S成功';
COMMENT ON COLUMN yy_job.execute_type IS '任务执行方式(S:标准扫描执行;L:本地执行;P:优先级(立即)执行)';
COMMENT ON COLUMN yy_job.business_no IS '业务号';
COMMENT ON COLUMN yy_job.expected_start_time IS '设置启动时间，为空则表示立即启动';
COMMENT ON COLUMN yy_job.exec_time IS '任务最新执行时间';
COMMENT ON COLUMN yy_job.exec_count IS '任务执行次数';
COMMENT ON COLUMN yy_job.version IS '版本号，乐观锁用';
COMMENT ON COLUMN yy_job.creator IS '创建者->对应ip和线程编号';
COMMENT ON COLUMN yy_job.gmt_created IS '创建时间';
COMMENT ON COLUMN yy_job.modifier IS '修改者->对应ip和线程编号';
COMMENT ON COLUMN yy_job.gmt_modified IS '修改时间';
COMMENT ON COLUMN yy_job.is_deleted IS '是否删除 Y/N';

CREATE TABLE yy_job_log (
  id BIGSERIAL PRIMARY KEY,
  job_id BIGINT NOT NULL,
  job_action CHAR(1) NOT NULL,
  exec_start_time TIMESTAMP DEFAULT NULL,
  exec_end_time TIMESTAMP DEFAULT NULL,
  manual_exec_user VARCHAR(64) DEFAULT NULL,
  err_msg VARCHAR(512) DEFAULT NULL,
  err_stack VARCHAR(4096) DEFAULT NULL,
  creator VARCHAR(64) NOT NULL,
  gmt_created TIMESTAMP NOT NULL,
  modifier VARCHAR(64) NOT NULL,
  gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  is_deleted CHAR(1) NOT NULL DEFAULT 'N'
);

CREATE INDEX idx_jobId ON yy_job_log(job_id);

COMMENT ON TABLE yy_job_log IS '作业执行日志表';
COMMENT ON COLUMN yy_job_log.id IS '主键';
COMMENT ON COLUMN yy_job_log.job_id IS '作业id,yy_job表主键';
COMMENT ON COLUMN yy_job_log.job_action IS '动作';
COMMENT ON COLUMN yy_job_log.exec_start_time IS '任务开始执行时间';
COMMENT ON COLUMN yy_job_log.exec_end_time IS '任务结束执行时间';
COMMENT ON COLUMN yy_job_log.manual_exec_user IS '人工执行用户';
COMMENT ON COLUMN yy_job_log.err_msg IS '作业执行错误信息';
COMMENT ON COLUMN yy_job_log.err_stack IS '作业执行错误堆栈';
COMMENT ON COLUMN yy_job_log.creator IS '创建者->对应ip和线程编号';
COMMENT ON COLUMN yy_job_log.gmt_created IS '创建时间';
COMMENT ON COLUMN yy_job_log.modifier IS '修改者->对应ip和线程编号';
COMMENT ON COLUMN yy_job_log.gmt_modified IS '修改时间';
COMMENT ON COLUMN yy_job_log.is_deleted IS '是否删除 Y/N';

CREATE TABLE yy_job_step (
  id BIGSERIAL PRIMARY KEY,
  job_id BIGINT NOT NULL,
  step_code VARCHAR(64) NOT NULL,
  step_name VARCHAR(64) NOT NULL,
  step_status CHAR(1) NOT NULL,
  step_params VARCHAR(2048) DEFAULT NULL,
  step_order INT DEFAULT NULL,
  exec_count INT DEFAULT NULL,
  exec_time TIMESTAMP DEFAULT NULL,
  creator VARCHAR(64) NOT NULL,
  gmt_created TIMESTAMP NOT NULL,
  modifier VARCHAR(64) NOT NULL,
  gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  is_deleted CHAR(1) NOT NULL DEFAULT 'N'
);

COMMENT ON TABLE yy_job_step IS '作业步骤表';
COMMENT ON COLUMN yy_job_step.id IS '主键';
COMMENT ON COLUMN yy_job_step.job_id IS '作业id, yy_job.id';
COMMENT ON COLUMN yy_job_step.step_code IS '步骤代码';
COMMENT ON COLUMN yy_job_step.step_name IS '步骤名称';
COMMENT ON COLUMN yy_job_step.step_status IS '步骤状态，C创建，R运行，F失败，S成功';
COMMENT ON COLUMN yy_job_step.step_params IS '步骤参数';
COMMENT ON COLUMN yy_job_step.step_order IS '步骤顺序';
COMMENT ON COLUMN yy_job_step.exec_count IS '执行次数';
COMMENT ON COLUMN yy_job_step.exec_time IS '最新执行时间';
COMMENT ON COLUMN yy_job_step.creator IS '创建者->对应ip和线程编号';
COMMENT ON COLUMN yy_job_step.gmt_created IS '创建时间';
COMMENT ON COLUMN yy_job_step.modifier IS '修改者->对应ip和线程编号';
COMMENT ON COLUMN yy_job_step.gmt_modified IS '修改时间';
COMMENT ON COLUMN yy_job_step.is_deleted IS '是否删除 Y/N';

CREATE TABLE yy_job_step_log (
  id BIGSERIAL PRIMARY KEY,
  job_id BIGINT NOT NULL,
  job_log_id BIGINT NOT NULL,
  job_step_id BIGINT NOT NULL,
  exec_start_time TIMESTAMP NOT NULL,
  exec_end_time TIMESTAMP DEFAULT NULL,
  job_step_result_flag CHAR(1) NOT NULL,
  job_step_result VARCHAR(1024) DEFAULT NULL,
  err_msg VARCHAR(512) DEFAULT NULL,
  err_stack VARCHAR(4096) DEFAULT NULL,
  creator VARCHAR(64) NOT NULL,
  gmt_created TIMESTAMP NOT NULL,
  modifier VARCHAR(64) NOT NULL,
  gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  is_deleted CHAR(1) NOT NULL DEFAULT 'N'
);

COMMENT ON TABLE yy_job_step_log IS '步骤执行日志表';
COMMENT ON COLUMN yy_job_step_log.id IS '主键';
COMMENT ON COLUMN yy_job_step_log.job_id IS '任务id,yy_job.id';
COMMENT ON COLUMN yy_job_step_log.job_log_id IS '任务日志id,yy_job_log.id';
COMMENT ON COLUMN yy_job_step_log.job_step_id IS '步骤id,yy_job_step.id';
COMMENT ON COLUMN yy_job_step_log.exec_start_time IS '步骤执行开始时间';
COMMENT ON COLUMN yy_job_step_log.exec_end_time IS '步骤执行结束时间';
COMMENT ON COLUMN yy_job_step_log.job_step_result_flag IS '步骤执行结果(Y:成功,N:失败)';
COMMENT ON COLUMN yy_job_step_log.job_step_result IS '步骤执行结果，执行器返回的JobResult对象';
COMMENT ON COLUMN yy_job_step_log.err_msg IS '步骤执行错误信息，执行过程中异常抛出来的';
COMMENT ON COLUMN yy_job_step_log.err_stack IS '步骤执行错误堆栈，执行过程中异常抛出来的';
COMMENT ON COLUMN yy_job_step_log.creator IS '创建者->对应ip和线程编号';
COMMENT ON COLUMN yy_job_step_log.gmt_created IS '创建时间';
COMMENT ON COLUMN yy_job_step_log.modifier IS '修改者->对应ip和线程编号';
COMMENT ON COLUMN yy_job_step_log.gmt_modified IS '修改时间';
COMMENT ON COLUMN yy_job_step_log.is_deleted IS '是否删除 Y/N';

-- 为 gmt_modified 字段添加自动更新触发器
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.gmt_modified = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_yy_job_log_updated
BEFORE UPDATE ON yy_job_log
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();

CREATE TRIGGER trigger_yy_job_step_updated
BEFORE UPDATE ON yy_job_step
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();

CREATE TRIGGER trigger_yy_job_step_log_updated
BEFORE UPDATE ON yy_job_step_log
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();
